/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CocoFood.DAO;

import CocoFood.utils.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author tinliensolution
 */
public abstract class DAObject {

    public DAObject() {
    }

    protected Connection getDBConnection() throws SQLException {
        Connection conn = null;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context) initCtx.lookup("java:comp/env");
            // Look up our data source
            DataSource ds = (DataSource) envCtx.lookup("jdbc/cocofood");
            // Allocate and use a connection from the pool
            conn = ds.getConnection();

        } catch (Exception e) {
            e.printStackTrace(System.out);
        }
        return conn;


    }

    public long getLastestIndex(String tableName) throws SQLException {
        long tblIndex = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sqlStmt = "SELECT * FROM tiso_index WHERE tbl_name=?";
        String sqlUpdate = "UPDATE tiso_index SET tbl_index=? WHERE tbl_name=?";
        try {
            conn = getDBConnection();
            pstmt = (PreparedStatement) conn.prepareStatement(sqlStmt);
            pstmt.setString(1, tableName);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                tblIndex = rs.getLong("tbl_index");
                long temp = tblIndex + 1;
                pstmt = (PreparedStatement) conn.prepareStatement(sqlUpdate);
                pstmt.setLong(1, temp);
                pstmt.setString(2, tableName);
                pstmt.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            DbUtils.closeQuietly(conn, pstmt, rs);
        }
        return tblIndex;
    }

    public long getLastestIndexWithoutIncrease(String tableName) throws SQLException {
        long result = 0;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sqlStmt = "SELECT *  "
                + "FROM tiso_index WHERE tbl_name=?";
        try {
            conn = getDBConnection();
            pstmt = (PreparedStatement) conn.prepareStatement(sqlStmt);
            pstmt.setString(1, tableName);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = Long.parseLong(rs.getString("tbl_index"));
            }
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }

        }
        return result;
    }

}
